package com.bdy.lm.browser.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;

import cn.itcast.jdbc.JdbcUtils;
import cn.itcast.jdbc.TxQueryRunner;

import com.bdy.lm.browser.domain.Command;
import com.bdy.lm.browser.domain.HistoryMsg;
import com.bdy.lm.browser.domain.RemoteManageQueryTaxi;

public class RemoteManageDao {

	private Connection con = null;// 定义引用
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private QueryRunner qr = new TxQueryRunner();

	public List<RemoteManageQueryTaxi> queryTaxi(String queryMode, String queryContent) {

		String sql = "SELECT car_tb.license,company_tb.companyName,driver_tb.driverName,online_tb.runState,online_tb.reportTime "
				+ "FROM car_tb,company_tb,online_tb,driver_tb "
				+ "WHERE car_tb.companyId=company_tb.companyId AND online_tb.ICID=driver_tb.ICID "
				+ "AND car_tb.ISUFlag=online_tb.ISUFlag ";
		Object[] param = { queryContent };
		try {
			if (queryMode.equals("all")) {
				return qr.query(sql, new BeanListHandler<RemoteManageQueryTaxi>(RemoteManageQueryTaxi.class));			
			} else if (queryMode.equals("companyName")) {
				String cn = sql + "AND companyName=?";
				return qr.query(cn, new BeanListHandler<RemoteManageQueryTaxi>(RemoteManageQueryTaxi.class), param);
			} else {
				String l = sql + "AND license=?";
				return qr.query(l, new BeanListHandler<RemoteManageQueryTaxi>(RemoteManageQueryTaxi.class), param);		
			} 
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public List<String> queryPartISUFlag(List<String> licenses) throws SQLException {
		
		List<String> ISUFlag = new ArrayList<String>();
		try {
			String sql = "SELECT ISUFlag FROM car_tb WHERE license=?";
			con = JdbcUtils.getConnection();
			for(String license : licenses) {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setString(1, license);
				rs = ps.executeQuery();
				rs.next();
				ISUFlag.add(rs.getString(1));
				//ps.clearParameters();//可以不加这一句
			}
			return ISUFlag;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			// 关闭
			 if (rs != null)
			 rs.close();
			 if (con != null)
			 con.close();
			 if (ps != null)
			 con.close();
		}
	}
	
	public List<Object> queryAllISUFlag() {
		try {
			String sql = "SELECT ISUFlag FROM car_tb";
			return qr.query(sql, new ColumnListHandler("ISUFlag"));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public boolean addCommand(List<Command> commands) {
		
		try {
			String sql = "INSERT INTO command_tb VALUES(?,?,?,?,?,?)";
			Object[][] params = new Object[commands.size()][];
			for(int i = 0; i< params.length; i ++) {
				Command c = commands.get(i);
				params[i] = new Object[]{ c.getISUFlag(), c.getMessageID(), c.getPublishTime(), c.getIsExecute(), c.getMsg(), c.getContent() };
			}
			int[] result = qr.batch(sql, params);
			int sum = 0;
			for (int i = 0; i < params.length; i++) {
				//System.out.println(result[i]+":\n");
				sum += result[i];
			}
			if (sum != params.length) {
				return false;
			} else {
				return true;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public List<HistoryMsg> queryHistoryMsg() {
		
		try {
			String sql = "SELECT DISTINCT publishTime,msg,content FROM command_tb";
			return qr.query(sql, new BeanListHandler<HistoryMsg>(HistoryMsg.class));
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public Map<String, List<String>> queryExecute(List<String> msgs) throws SQLException {
		
		Map<String, List<String>> result = new HashMap<String, List<String>>();
		try {
			String sql = "SELECT DISTINCT car_tb.license,command_tb.content FROM command_tb,car_tb " 
							+ "WHERE command_tb.msg=? AND command_tb.isExecute='no' AND car_tb.ISUFlag=command_tb.ISUFlag";
			con = JdbcUtils.getConnection();
			for(String msg : msgs) {
				ps = con.prepareStatement(sql);
				ps.setString(1, msg);
				rs = ps.executeQuery();
				List<String> s = new ArrayList<String>();
				String c = "";
				while(rs.next()) {
					 c = rs.getString(2);
					 s.add(rs.getString(1));
				}
				result.put(c, s);
			}
			return result;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			// 关闭
			 if (rs != null)
			 rs.close();
			 if (con != null)
			 con.close();
			 if (ps != null)
			 con.close();
		}
	}
}
